package org.compiere.model;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

import extend.org.compiere.utils.Util;

public class MBPMStandartHours extends X_BPM_StandartHours {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 2617817916610958551L;

	public MBPMStandartHours(Properties ctx) {
		super(ctx);
	}

	public MBPMStandartHours(Properties ctx, int BPM_StandartHours_ID,
			String trxName) {
		super(ctx, BPM_StandartHours_ID, trxName);
	}

	public MBPMStandartHours(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
	}
	
	@Override
	protected boolean afterSave(boolean newRecord, boolean success) {
		
		// Error message
		StringBuilder errmsg = new StringBuilder();
		
		if(newRecord){
			
			if(getCountEmployee()==0) 
				errmsg.append(Util.requiredField(COLUMNNAME_CountEmployee));
			Util.checkErrMsg(errmsg);
			
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			
			// List job Name
			String sql_ = "select trim(name) from hr_job \n" +
					"where isActive='Y' group by trim(name)";
			
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				rs = pstmt.executeQuery();
				while(rs.next()){
					if(!rs.getString(1).isEmpty()){
						MBPMStandartJob stJob = new MBPMStandartJob(getCtx(), 0, get_TrxName());
						stJob.setName(rs.getString(1));
						stJob.setBPM_StandartHours_ID(getBPM_StandartHours_ID());
						stJob.sethrm_amountsalary(new BigDecimal(0));
						stJob.setPayrollAmount(new BigDecimal(0));
						stJob.sethrm_CNalog(new BigDecimal(0));
						stJob.saveEx();
					}
				}
			}
			catch (SQLException e)
			{
				CLogger.get().log(Level.INFO, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			
			saveStandartCharge(getBPM_Project_ID());
		}
		
		return true;
	}
	
	private void saveStandartCharge(int BPM_Project_ID){
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// 
		String sql_ = "select chargeName, c_charge_id, amount from bpm_standartCharge_v \n" +
				"WHERE BPM_Project_ID="+BPM_Project_ID+" \n" +
				"ORDER BY fi_code";
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			rs = pstmt.executeQuery();	
			while(rs.next()){
				if(!MbpmstandartCharge.isValues(getBPM_StandartHours_ID(), rs.getInt(3))){
					MbpmstandartCharge stCh = new MbpmstandartCharge(getCtx(), 0, get_TrxName());
					stCh.setBPM_StandartHours_ID(getBPM_StandartHours_ID());
					stCh.setChargeName(rs.getString(1));
					stCh.setC_Charge_ID(rs.getInt(2));
					stCh.setamountemployee(new BigDecimal(Math.round(rs.getDouble(3)/getCountEmployee())));
					stCh.setAmount(new BigDecimal(Math.round(rs.getDouble(3))));
					stCh.setCountEmployee(getCountEmployee());
					stCh.saveEx();
				}
			}				
		}
		catch (SQLException e)
		{
			CLogger.get().log(Level.INFO, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}		
		
	}
		
	public static MBPMStandartHours getMBPMStandartHours(int BPM_Project_ID){
		
		MBPMStandartHours standartHours = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		
		String sql = "SELECT "+I_BPM_StandartHours.COLUMNNAME_BPM_StandartHours_ID+" \n " +
				"FROM "+I_BPM_StandartHours.Table_Name+" \n" +
				"WHERE "+I_BPM_StandartHours.COLUMNNAME_BPM_Project_ID+"="+BPM_Project_ID;
		try
		{
			pstmt = DB.prepareStatement(sql,null);
			rs = pstmt.executeQuery();	
			if(rs.next ())
				standartHours = new MBPMStandartHours(Env.getCtx(), rs.getInt(1), null);
		}
		catch (SQLException e)
		{
			CLogger.get().log(Level.INFO, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return standartHours;		
	}
	
	public static MBPMStandartJob[] getLinesJob(Properties ctx, int BPM_Project_ID, String trxName){
		
		MBPMStandartHours standartH = MBPMStandartHours.getMBPMStandartHours(BPM_Project_ID);
		
		if(standartH == null)
			return new MBPMStandartJob[0];
		
		List<MBPMStandartJob> list = new Query(ctx, I_BPM_StandartJob.Table_Name, I_BPM_StandartJob.COLUMNNAME_BPM_StandartHours_ID+"=?", trxName)
		.setParameters(standartH.getBPM_StandartHours_ID())
		.setOnlyActiveRecords(true)
		.list();
		
		MBPMStandartJob[] retValue = new MBPMStandartJob[list.size ()];
		
		list.toArray(retValue);
		
		return retValue;
	}
	
	public static double getCosts(int BPM_StandartHours_ID){
		
		int result = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		
		String sql = "SELECT round(sum(amountemployee)) FROM bpm_standartCharge \n" +
				"WHERE "+I_BPM_StandartHours.COLUMNNAME_BPM_StandartHours_ID+"="+BPM_StandartHours_ID+" \n" +
				"AND isActive='Y' ";
		try
		{
			pstmt = DB.prepareStatement(sql,null);
			rs = pstmt.executeQuery();	
			if(rs.next ())
				return rs.getDouble(1);
		}
		catch (SQLException e)
		{
			CLogger.get().log(Level.INFO, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return result;
		
	}
	
}
